CREATE PROCEDURE Setting_FindAll
AS
BEGIN
    Select 
    SettingId,
    SettingName,
    SettingValue,
    ApplicationId
    from Setting
END
GO

CREATE PROCEDURE Setting_FindById
@SettingId int
AS
BEGIN
    Select * from Setting where SettingId = @SettingId
END
GO

CREATE PROCEDURE Setting_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Setting where SettingId = @name
END
GO

CREATE PROCEDURE Setting_Insert
        @SettingId int ,
        @SettingName nvarchar (50),
        @SettingValue nvarchar (50),
        @ApplicationId int 
AS
BEGIN
INSERT INTO Setting
(
        SettingId,
        SettingName,
        SettingValue,
        ApplicationId
)
VALUES
(
        @SettingId,
        @SettingName,
        @SettingValue,
        @ApplicationId
)
select @SettingId = @@IDENTITY
END
GO

CREATE PROCEDURE Setting_Update
        @SettingId int ,
        @SettingName nvarchar (50),  
        @SettingValue nvarchar (50),  
        @ApplicationId int
AS
BEGIN
UPDATE  Setting SET
    SettingName = @SettingName,
    SettingValue = @SettingValue,
    ApplicationId = @ApplicationId
    WHERE SettingId = @SettingId
END
GO

CREATE PROCEDURE Setting_Delete
    @SettingId int
AS
BEGIN
    DELETE FROM Setting where SettingId = @SettingId
END
GO

CREATE PROCEDURE Setting_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(SettingId) FROM Setting;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Setting
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Setting_SetActive
@SettingId int
AS
declare @Type bit
SELECT @Type =  IsActive from Setting where SettingId = @SettingId
BEGIN
IF(@Type = 1)
Update Setting set IsActive = 0 where SettingId = @SettingId
else
Update Setting set IsActive = 1 where SettingId = @SettingId
END
GO

CREATE proc Setting_SetSortIndex
@SettingId int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Setting where SettingId = @SettingId
BEGIN
IF(@Type = 1)
Update Setting set SortIndex =  @CurrentSort + 1 where SettingId = @SettingId
else
Update Setting set SortIndex =  @CurrentSort - 1 where SettingId = @SettingId
END
GO

CREATE PROCEDURE Setting_RemoveSelectedItem
    @SettingId int
AS
BEGIN
    UPDATE Setting SET IsDeleted = 1 WHERE SettingId = @SettingId
END
GO

CREATE PROCEDURE Setting_RestoreSelectedItem
    @SettingId int
AS
BEGIN
    UPDATE Setting SET IsDeleted = 0 WHERE SettingId = @SettingId
END
GO

CREATE PROCEDURE Setting_RemoveAll
    @SettingId int
AS
BEGIN
    UPDATE Setting SET IsDeleted = 1
END
GO

CREATE PROCEDURE Setting_RestoreAll
    @SettingId int
AS
BEGIN
    UPDATE Setting SET IsDeleted = 0
END
GO

